package com.iot.inclinometer.dao.impl;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.iot.inclinometer.dao.IObserveInfoDao;
import com.iot.inclinometer.db.DBHelper;
import com.iot.inclinometer.pojo.ObserveInfo;

import java.util.ArrayList;
import java.util.List;

public class ObserveInfoDaoImpl implements IObserveInfoDao {
	private DBHelper helper;

	public ObserveInfoDaoImpl(Context context) {
		helper = new DBHelper(context);
	}

	@Override
	public int insert(ObserveInfo info){
		int _id = 0;
		SQLiteDatabase database = null;
		try {
			String sql1 = "INSERT INTO observe_info(observer,time,interval,measurecount,slipdeep,base_id,count) VALUES (?,?,?,?,?,?,?);";
			database = helper.getWritableDatabase();
			database.execSQL(
					sql1,
					new Object[] { info.getObserver(), 
							info.getTime(), info.getInterval(),
							info.getMeasurecount(), info.getSlipdeep(),
							info.getBaseId(), info.getCount() });
			// 用这么多条件是为了保证记录的唯一性
			String sql2 = "select * from observe_info where measurecount=? and base_id=? and count=?";

			Cursor cursor = database.rawQuery(
					sql2,
					new String[] { info.getMeasurecount(),
							Integer.toString(info.getBaseId()),
							Integer.toString(info.getCount()) });
			while (cursor.moveToNext()) {
				_id = cursor.getInt(cursor.getColumnIndex("ob_id"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		// Log.i("info", "观测信息插入的数据的ID是：" + _id);
		return _id;
	}
    @Override
	public String getLastObserverByBaseId(int baseId){
		List<ObserveInfo> all = new ArrayList<ObserveInfo>();
		SQLiteDatabase database = null;
		try {
			String sql = "select * from observe_info where base_id=?";
			database = helper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sql, new String[] {Integer.toString(baseId) });
			while (cursor.moveToNext()) {
				int _obId = cursor.getInt(cursor.getColumnIndex("ob_id"));
				String _observer = cursor.getString(cursor
						.getColumnIndex("observer"));
				String _time = cursor.getString(cursor.getColumnIndex("time"));
				String _interval = cursor.getString(cursor
						.getColumnIndex("interval"));
				String _measurecount = cursor.getString(cursor
						.getColumnIndex("measurecount"));
				String _slipdeep = cursor.getString(cursor
						.getColumnIndex("slipdeep"));
				
				int _baseId = cursor.getInt(cursor.getColumnIndex("base_id"));
				int _count = cursor.getInt(cursor.getColumnIndex("count"));

				ObserveInfo observeInfo = new ObserveInfo();
				observeInfo.setObserveId(_obId);
				observeInfo.setObserver(_observer);
				observeInfo.setTime(_time);
				observeInfo.setInterval(_interval);
				observeInfo.setMeasurecount(_measurecount);
				observeInfo.setSlipdeep(_slipdeep);
				observeInfo.setBaseId(_baseId);
				observeInfo.setCount(_count);
				all.add(observeInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		
		String lastObserver=null;
		if(all.size()>0){
			ObserveInfo lastObserveInfo=all.get(all.size()-1);
			lastObserver=lastObserveInfo.getObserver();
		
		}
		return lastObserver;
	}
	@Override
	public String getLastIntervalByBaseId(int baseId){
		List<ObserveInfo> all = new ArrayList<ObserveInfo>();
		SQLiteDatabase database = null;
		try {
			String sql = "select * from observe_info where base_id=?";
			database = helper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sql, new String[] {Integer.toString(baseId) });
			while (cursor.moveToNext()) {
				int _obId = cursor.getInt(cursor.getColumnIndex("ob_id"));
				String _observer = cursor.getString(cursor
						.getColumnIndex("observer"));
				String _time = cursor.getString(cursor.getColumnIndex("time"));
				String _interval = cursor.getString(cursor
						.getColumnIndex("interval"));
				String _measurecount = cursor.getString(cursor
						.getColumnIndex("measurecount"));
				String _slipdeep = cursor.getString(cursor
						.getColumnIndex("slipdeep"));
				
				int _baseId = cursor.getInt(cursor.getColumnIndex("base_id"));
				int _count = cursor.getInt(cursor.getColumnIndex("count"));

				ObserveInfo observeInfo = new ObserveInfo();
				observeInfo.setObserveId(_obId);
				observeInfo.setObserver(_observer);
				observeInfo.setTime(_time);
				observeInfo.setInterval(_interval);
				observeInfo.setMeasurecount(_measurecount);
				observeInfo.setSlipdeep(_slipdeep);
				observeInfo.setBaseId(_baseId);
				observeInfo.setCount(_count);
				all.add(observeInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		
		String lastInterval=null;
		if(all.size()>0){
			ObserveInfo lastObserveInfo=all.get(all.size()-1);
			lastInterval=lastObserveInfo.getInterval();		
		}
		return lastInterval;
	}
	@Override
	public List<ObserveInfo> selectObserverInfos(int baseId) {
		List<ObserveInfo> all = new ArrayList<ObserveInfo>();

		SQLiteDatabase database = null;
		try {
			String sql = "select * from observe_info where base_id=?";
			database = helper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sql,
					new String[] { Integer.toString(baseId) });
			while (cursor.moveToNext()) {
				int _obId = cursor.getInt(cursor.getColumnIndex("ob_id"));
				String _observer = cursor.getString(cursor
						.getColumnIndex("observer"));
				String _time = cursor.getString(cursor.getColumnIndex("time"));
				String _interval = cursor.getString(cursor
						.getColumnIndex("interval"));
				String _measurecount = cursor.getString(cursor
						.getColumnIndex("measurecount"));
				String _slipdeep = cursor.getString(cursor
						.getColumnIndex("slipdeep"));
				int _baseId = cursor.getInt(cursor.getColumnIndex("base_id"));
				int _count = cursor.getInt(cursor.getColumnIndex("count"));
				ObserveInfo observeInfo = new ObserveInfo();
				observeInfo.setObserveId(_obId);
				observeInfo.setObserver(_observer);
				observeInfo.setTime(_time);
				observeInfo.setInterval(_interval);
				observeInfo.setMeasurecount(_measurecount);
				observeInfo.setSlipdeep(_slipdeep);
				observeInfo.setBaseId(_baseId);
				observeInfo.setCount(_count);
				all.add(observeInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		return all;
	}
	@Override
	//
	public List<ObserveInfo> selectObserverInfos() {
		List<ObserveInfo> all = new ArrayList<ObserveInfo>();
		SQLiteDatabase database = null;
		try {
			String sql = "select * from observe_info";
			database = helper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sql,null);
			while (cursor.moveToNext()) {
				int _obId = cursor.getInt(cursor.getColumnIndex("ob_id"));
				String _observer = cursor.getString(cursor
						.getColumnIndex("observer"));
				String _time = cursor.getString(cursor.getColumnIndex("time"));
				String _interval = cursor.getString(cursor
						.getColumnIndex("interval"));
				String _measurecount = cursor.getString(cursor
						.getColumnIndex("measurecount"));
				String _slipdeep = cursor.getString(cursor
						.getColumnIndex("slipdeep"));
				int _baseId = cursor.getInt(cursor.getColumnIndex("base_id"));
				int _count = cursor.getInt(cursor.getColumnIndex("count"));
				ObserveInfo observeInfo = new ObserveInfo();
				observeInfo.setObserveId(_obId);
				observeInfo.setObserver(_observer);
				observeInfo.setTime(_time);
				observeInfo.setInterval(_interval);
				observeInfo.setMeasurecount(_measurecount);
				observeInfo.setSlipdeep(_slipdeep);
				observeInfo.setBaseId(_baseId);
				observeInfo.setCount(_count);
				all.add(observeInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		return all;
	}
	
	@Override
	public List<ObserveInfo> selectObserverInfos(int baseId, String measurecount) {
		List<ObserveInfo> all = new ArrayList<ObserveInfo>();

		SQLiteDatabase database = null;
		try {
			String sql = "select * from observe_info where measurecount=? and base_id=?";
			database = helper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sql, new String[] { measurecount,
					Integer.toString(baseId) });
			while (cursor.moveToNext()) {
				int _obId = cursor.getInt(cursor.getColumnIndex("ob_id"));
				String _observer = cursor.getString(cursor
						.getColumnIndex("observer"));
				String _time = cursor.getString(cursor.getColumnIndex("time"));
				String _interval = cursor.getString(cursor
						.getColumnIndex("interval"));
				String _measurecount = cursor.getString(cursor
						.getColumnIndex("measurecount"));
				String _slipdeep = cursor.getString(cursor
						.getColumnIndex("slipdeep"));
				int _baseId = cursor.getInt(cursor.getColumnIndex("base_id"));
				int _count = cursor.getInt(cursor.getColumnIndex("count"));
				ObserveInfo observeInfo = new ObserveInfo();
				observeInfo.setObserveId(_obId);
				observeInfo.setObserver(_observer);
				observeInfo.setTime(_time);
				observeInfo.setInterval(_interval);
				observeInfo.setMeasurecount(_measurecount);
				observeInfo.setSlipdeep(_slipdeep);
				observeInfo.setBaseId(_baseId);
				observeInfo.setCount(_count);
				all.add(observeInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		return all;
	}

	@Override
	public List<ObserveInfo> selectObserveInfos(int baseId, String measurecount,
			String dateFrom, String dateEnd) {
		List<ObserveInfo> all = new ArrayList<ObserveInfo>();

		SQLiteDatabase database = null;
		String sql;
		Cursor cursor;
		try {
			if(dateFrom.equals(dateEnd)){
				sql = "select * from observe_info where measurecount=? and base_id=? and time like ?";
				database = helper.getReadableDatabase();
				cursor = database.rawQuery(sql, new String[] { measurecount,
						Integer.toString(baseId), dateFrom+"%" });
			}else{
				sql = "select * from observe_info where measurecount=? and base_id=? and time between ? and ?";
				database = helper.getReadableDatabase();
				cursor = database.rawQuery(sql, new String[] { measurecount,
						Integer.toString(baseId), dateFrom, dateEnd });
			}	
			while (cursor.moveToNext()) {
				int _obId = cursor.getInt(cursor.getColumnIndex("ob_id"));
				String _observer = cursor.getString(cursor
						.getColumnIndex("observer"));
				String _time = cursor.getString(cursor.getColumnIndex("time"));
				String _interval = cursor.getString(cursor
						.getColumnIndex("interval"));
				String _measurecount = cursor.getString(cursor
						.getColumnIndex("measurecount"));
				String _slipdeep = cursor.getString(cursor
						.getColumnIndex("slipdeep"));
				int _baseId = cursor.getInt(cursor.getColumnIndex("base_id"));
				int _count = cursor.getInt(cursor.getColumnIndex("count"));

				ObserveInfo observeInfo = new ObserveInfo();
				observeInfo.setObserveId(_obId);
				observeInfo.setObserver(_observer);
				observeInfo.setTime(_time);
				observeInfo.setInterval(_interval);
				observeInfo.setMeasurecount(_measurecount);
				observeInfo.setSlipdeep(_slipdeep);
				observeInfo.setBaseId(_baseId);
				observeInfo.setCount(_count);
				all.add(observeInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		return all;
	}

	@Override
	public List<ObserveInfo> selectObserveInfos(String dateFrom, String dateEnd) {
		List<ObserveInfo> all = new ArrayList<ObserveInfo>();

		SQLiteDatabase database = null;
		try {
			String sql = "select * from observe_info where time between ? and ?";
			database = helper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sql, new String[] { dateFrom,
					dateEnd });
			while (cursor.moveToNext()) {
				int _obId = cursor.getInt(cursor.getColumnIndex("ob_id"));
				String _observer = cursor.getString(cursor
						.getColumnIndex("observer"));
				String _time = cursor.getString(cursor.getColumnIndex("time"));
				String _interval = cursor.getString(cursor
						.getColumnIndex("interval"));
				String _measurecount = cursor.getString(cursor
						.getColumnIndex("measurecount"));
				String _slipdeep = cursor.getString(cursor
						.getColumnIndex("slipdeep"));
				int _baseId = cursor.getInt(cursor.getColumnIndex("base_id"));
				int _count = cursor.getInt(cursor.getColumnIndex("count"));
				ObserveInfo observeInfo = new ObserveInfo();
				observeInfo.setObserveId(_obId);
				observeInfo.setObserver(_observer);
				observeInfo.setTime(_time);
				observeInfo.setInterval(_interval);
				observeInfo.setMeasurecount(_measurecount);
				observeInfo.setSlipdeep(_slipdeep);
				observeInfo.setBaseId(_baseId);
				observeInfo.setCount(_count);
				all.add(observeInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		return all;
	}

	@Override
	public boolean update(ObserveInfo observeInfo) {

		boolean flag = false;

		SQLiteDatabase database = null;
		try {
			String sql = "UPDATE observe_info set observer=?,time=?,interval=?,measurecount=?,slipdeep=?,base_id=?,count=? Where ob_id=?";
			database = helper.getWritableDatabase();
			database.execSQL(sql, new Object[] { observeInfo.getObserver(),observeInfo.getTime(),
					observeInfo.getInterval(), observeInfo.getMeasurecount(),
					observeInfo.getSlipdeep(), observeInfo.getBaseId(),
					observeInfo.getCount(), observeInfo.getObserveId() });
			flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}

		return flag;

	}

	public ObserveInfo selectObserveInfo(String measurecount,int baseId,int count)
	{
		ObserveInfo observeInfo = new ObserveInfo();
		SQLiteDatabase database = null;
		try {
			String sql = "select * from observe_info where measurecount=? and base_id=? and count=?";
			database = helper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sql, new String[] { measurecount,
					Integer.toString(baseId),Integer.toString(count)});
			while (cursor.moveToNext()) {
				int _obId = cursor.getInt(cursor.getColumnIndex("ob_id"));
				String _observer = cursor.getString(cursor
						.getColumnIndex("observer"));
				String _time = cursor.getString(cursor.getColumnIndex("time"));
				String _interval = cursor.getString(cursor
						.getColumnIndex("interval"));
				String _measurecount = cursor.getString(cursor
						.getColumnIndex("measurecount"));
				String _slipdeep = cursor.getString(cursor
						.getColumnIndex("slipdeep"));
				int _baseId = cursor.getInt(cursor.getColumnIndex("base_id"));
				int _count = cursor.getInt(cursor.getColumnIndex("count"));

				observeInfo.setObserveId(_obId);
				observeInfo.setObserver(_observer);
				observeInfo.setTime(_time);
				observeInfo.setInterval(_interval);
				observeInfo.setMeasurecount(_measurecount);
				observeInfo.setSlipdeep(_slipdeep);
				observeInfo.setBaseId(_baseId);
				observeInfo.setCount(_count);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		return observeInfo;
	}
	/**
	 * 通过obId删除观测信息数据
	 */
	@Override
	public int deleteObserveInfoByobId(int obId){
		//得到可写的SQLiteDatabase对象  
        SQLiteDatabase database = helper.getWritableDatabase();  
        //调用delete方法，删除数据  
        int flag=database.delete("observe_info", "ob_id=?", new String[]{Integer.toString(obId)});  
        return flag;
	}
	@Override
	public List<ObserveInfo> selectObserverInfoscount(int obId) {
		List<ObserveInfo> all = new ArrayList<ObserveInfo>();

		SQLiteDatabase database = null;
		try {
			String sql = "select * from observe_info where ob_id=?";
			database = helper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sql,
					new String[] { Integer.toString(obId) });
			while (cursor.moveToNext()) {
				int _obId = cursor.getInt(cursor.getColumnIndex("ob_id"));
				String _observer = cursor.getString(cursor
						.getColumnIndex("observer"));
				String _time = cursor.getString(cursor.getColumnIndex("time"));
				String _interval = cursor.getString(cursor
						.getColumnIndex("interval"));
				String _measurecount = cursor.getString(cursor
						.getColumnIndex("measurecount"));
				String _slipdeep = cursor.getString(cursor
						.getColumnIndex("slipdeep"));
				int _baseId = cursor.getInt(cursor.getColumnIndex("base_id"));
				int _count = cursor.getInt(cursor.getColumnIndex("count"));
				ObserveInfo observeInfo = new ObserveInfo();
				observeInfo.setObserveId(_obId);
				observeInfo.setObserver(_observer);
				observeInfo.setTime(_time);
				observeInfo.setInterval(_interval);
				observeInfo.setMeasurecount(_measurecount);
				observeInfo.setSlipdeep(_slipdeep);
				observeInfo.setBaseId(_baseId);
				observeInfo.setCount(_count);
				all.add(observeInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
		return all;
	}
	@Override
	public int getLastCountByBaseId(int baseId) {
		List<ObserveInfo> all = new ArrayList<ObserveInfo>();
		SQLiteDatabase database = null;
		try {
			String sql = "select * from observe_info where base_id=?";
			database = helper.getReadableDatabase();
			Cursor cursor = database.rawQuery(sql, new String[] {Integer.toString(baseId) });
			while (cursor.moveToNext()) {
				int _obId = cursor.getInt(cursor.getColumnIndex("ob_id"));
				String _observer = cursor.getString(cursor
						.getColumnIndex("observer"));
				String _time = cursor.getString(cursor.getColumnIndex("time"));
				String _interval = cursor.getString(cursor
						.getColumnIndex("interval"));
				String _measurecount = cursor.getString(cursor
						.getColumnIndex("measurecount"));
				String _slipdeep = cursor.getString(cursor
						.getColumnIndex("slipdeep"));
				int _baseId = cursor.getInt(cursor.getColumnIndex("base_id"));
				int _count = cursor.getInt(cursor.getColumnIndex("count"));

				ObserveInfo observeInfo = new ObserveInfo();
				observeInfo.setObserveId(_obId);
				observeInfo.setObserver(_observer);
				observeInfo.setTime(_time);
				observeInfo.setInterval(_interval);
				observeInfo.setMeasurecount(_measurecount);
				observeInfo.setSlipdeep(_slipdeep);
				observeInfo.setBaseId(_baseId);
				observeInfo.setCount(_count);
				all.add(observeInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (database != null) {
				database.close();
			}
		}
        int lastObserverCount=0;
		if(all.size()>0){
			ObserveInfo lastObserveInfo=all.get(all.size()-2);
			lastObserverCount=lastObserveInfo.getCount();
		}
		return lastObserverCount;
	}

	@Override
	public List<ObserveInfo> selectCountInfoscount(int obId) {
		// TODO Auto-generated method stub
		return null;
	}
	@Override
	public int deleteAllObserverInfos() {
		//得到可写的SQLiteDatabase对象  
        SQLiteDatabase database = helper.getWritableDatabase();  
        String sql = "delete from observe_info";
        database.execSQL(sql);
        database.close();
        //调用delete方法，删除数据  
        int flag=1;
        return flag;
	}
}
